package com.informet.ipd.modelo.dao;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */



import com.informet.ipd.modelo.vo.CrecerConDeporteVO;
import com.informet.ipd.modelo.vo.ProgEstrVO;


import com.informet.ipd.modelo.vo.UbigeoVO;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author LAPTOP01
 */
public class EditNotaProcesoEvaluacionDao extends DBase{


  Connection conn = null;

    public EditNotaProcesoEvaluacionDao() {
        super();
    }


////Ini carga de combo para causa retiro
     public void  setEtiquetas(CrecerConDeporteVO bean, ResultSet rs) throws SQLException{

           bean.setCodEtiqueta(rs.getString("NUM_CODcausaret_pk"));
           bean.setDescEtiqueta(rs.getString("tx_desccausaret"));

    }

    public List getListEtiquetas(String id,String trimestre)  throws SQLException {
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<CrecerConDeporteVO> lista = new ArrayList<CrecerConDeporteVO>(0);

        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();

                sql = "  select * from ME_causaret ";


            rs = stmt.executeQuery(sql);
            CrecerConDeporteVO bean = null;
            while (rs.next()) {
                bean = new CrecerConDeporteVO();
                setEtiquetas(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }
////Fin carga de combo para causa retiro

    public void  setListNotaProcesoEvaluacion(CrecerConDeporteVO bean, ResultSet rs) throws SQLException{

          bean.setNombre(rs.getString("nombre"));
          bean.setCodAspTecnico(rs.getString("num_codasptec_pk"));
          bean.setCodCcd(Integer.parseInt(rs.getString("num_ccd_pk")));
          bean.setNumNotaEvatec(rs.getString("num_notaevatec"));
          bean.setNumCodEventoTriemsre(rs.getString("num_codeventotrimestre_pk"));
          bean.setDescAspTecnico(rs.getString("tx_descasptec"));
          bean.setCodAlumno(rs.getString("num_codalumccd_pk"));
          bean.setNumEntradaEt(rs.getString("notaEntrada"));
          bean.setDescripcionAspectos(rs.getString("tip"));
          bean.setNumCodNotaEvatec(rs.getString("num_codnotaevatec_pk"));
          
          //bean.setDescEvento(rs.getString("tx_descevento"));
          

    }


public List getListNotaProcesoEvaluacionEvaluacionTecnica(String idAlumno,String idCcd,String idEventoTrimestre) throws SQLException {

        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<CrecerConDeporteVO> lista = new ArrayList<CrecerConDeporteVO>(0);
        String sql = "";
        
        try {
            conn = DriverManager.getConnection(url, usuario, password);            
            stmt = conn.createStatement();
                sql = " select "+
                      "       distinct((a.tx_nombreaccd+' '+ a.tx_apaternoaccd+' '+ a.tx_amaternoaccd)) as nombre, "+
                      "       'Evaluacion Tec' as tip,  "+
                      "       ne.num_codasptec_pk, "+
                      "       isnull(ne.num_notaevatec,'') as num_notaevatec, "+
                      "       at.tx_descasptec, "+
                      "       a.num_codalumccd_pk, "+
                      "       a.num_ccd_pk, "+
                      "       ne.num_codeventotrimestre_pk, "+
                      "       ne.num_codnotaevatec_pk, "+
                      "       (select  "+
                      "               num_entradaet  "+
                      "        from me_evatec  "+
                      "        where num_codalumccd_pk=ne.num_codalumccd_pk and "+
                      "                  num_ccd_pk=ne.num_ccd_pk and  "+
                      "                  num_codasptec_pk=ne.num_codasptec_pk "+
                      "       ) as notaEntrada "+
                      "   from me_notaevatec ne INNER JOIN me_asptecnicos at on at.num_codasptec_pk=ne.num_codasptec_pk "+
                      "                         inner join me_alumnosccd a on a.num_codalumccd_pk=ne.num_codalumccd_pk "+
                      "                                    and a.num_ccd_pk=ne.num_ccd_pk "+
                      "   where ne.num_codeventotrimestre_pk="+idEventoTrimestre+
                      "         and ne.num_codalumccd_pk="+idAlumno+
                      "         and ne.num_ccd_pk="+idCcd;

            rs = stmt.executeQuery(sql);
            CrecerConDeporteVO bean1 = null;
            while (rs.next()) {
                bean1 = new CrecerConDeporteVO();
                setListNotaProcesoEvaluacion(bean1, rs);
                lista.add(bean1);
            }
           
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
      // JOptionPane.showMessageDialog(null, lista.size());
        return lista;
    }

public List getListNotaProcesoEvaluacionEvaluacionFisica(String idAlumno,String idCcd,String idEventoTrimestre) throws SQLException {

        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<CrecerConDeporteVO> lista = new ArrayList<CrecerConDeporteVO>(0);
        String sql = "";

        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
                        sql = " select "+
                              "         distinct((a.tx_nombreaccd+' '+ a.tx_apaternoaccd+' '+ a.tx_amaternoaccd)) as nombre, "+
                              "         'Evaluacion Fis' as tip,  "+
                              "          ne.num_codaspfis_pf as num_codasptec_pk , "+
                              "          isnull(ne.num_notaevafis,'') as num_notaevatec, "+
                              "          af.tx_descaspfis as tx_descasptec, "+
                              "          a.num_codalumccd_pk, "+
                              "          a.num_ccd_pk, "+
                              "          ne.num_codeventotrimestre_pk, "+
                              "          ne.num_codnotaevafis_pk as num_codnotaevatec_pk, "+
                              "          (select  "+
                              "                  num_entradaef  "+
                              "           from me_evafis "+
                              "           where num_codalumccd_pk=ne.num_codalumccd_pk and "+
                              "                     num_ccd_pk=ne.num_ccd_pk and  "+
                              "                     num_codaspfis_pf=ne.num_codaspfis_pf "+
                              "          ) as notaEntrada "+
                              "  from me_notaevafis ne INNER JOIN me_aspfisicos af on af.num_codaspfis_pf=ne.num_codaspfis_pf "+
                              "                                            inner join me_alumnosccd a on a.num_codalumccd_pk=ne.num_codalumccd_pk "+
                              "                                                           and a.num_ccd_pk=ne.num_ccd_pk "+
                              "  where ne.num_codeventotrimestre_pk="+idEventoTrimestre+
                              "            and ne.num_codalumccd_pk="+idAlumno+
                              "            and ne.num_ccd_pk="+idCcd;
            rs = stmt.executeQuery(sql);
            CrecerConDeporteVO bean1 = null;
            while (rs.next()) {
                bean1 = new CrecerConDeporteVO();
                setListNotaProcesoEvaluacion(bean1, rs);
                lista.add(bean1);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
      // JOptionPane.showMessageDialog(null, lista.size());
        return lista;
    }


    public String spMan(String nota,String codAlumnoHidden,String codCcdHidden,String codAspTecnicoHidden,String numCodEventoTriemsreHidden,String descripcionAspectosHidden,String numCodNotaEvatecHidden, String  codEtiqueta) {
        String msj = "";
        //String codP=bean.getCodPrograma();
        try {

            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_update_MeNotaProcesoEvaluacion(?,?,?,?,?,?,?,?)}");
            //codActividad, codAnio, titu_programa,justificacion,fechaInicio, fechaFin,monto
            cs.setString(1, nota);
            cs.setString(2, codAlumnoHidden);
            cs.setString(3, codCcdHidden);
            cs.setString(4, codAspTecnicoHidden);
            cs.setString(5, numCodEventoTriemsreHidden);
            cs.setString(6, descripcionAspectosHidden);
            cs.setString(7, numCodNotaEvatecHidden);
            cs.setString(8, codEtiqueta);
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }

    public String spManCausaRetiro(String codEtiqueta,String nombEtiqueta,String tipoOper) {
        String msj = "";
        //String codP=bean.getCodPrograma();
        try {

            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_update_MeCausaRetProcEval(?,?,?)}");
            //codActividad, codAnio, titu_programa,justificacion,fechaInicio, fechaFin,monto
            cs.setString(1, codEtiqueta);
            cs.setString(2, nombEtiqueta);
            cs.setString(3, tipoOper);
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }

/*
      public String spMan(CrecerConDeporteVO bean) {
        String msj = "";
        //String codP=bean.getCodPrograma();
        try {
        	
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_update_MeEveTriNotaEvatec(?,?,?,?,?,?,?,?,?,?)}");
            //codActividad, codAnio, titu_programa,justificacion,fechaInicio, fechaFin,monto
            cs.setString(1, bean.getCodAlumno());
            cs.setString(2, bean.getApePaterno());
            cs.setString(3, bean.getApeMaterno());
            cs.setString(4, bean.getNombre());
            cs.setString(5, bean.getFecNacimiento());
            cs.setString(6, bean.getCondicion());
            cs.setString(7, bean.getPeso());
            cs.setString(8, bean.getTalla());
            cs.setInt(9, bean.getCodCcd());
            cs.setString(10, bean.getTipoOper());
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }
*/
}
